
	***************
	** Calculate life expectancy and healthy life expectancy statistics for paper
	***************
	
	** Life expectancy
	
		insheet using "$dir/rawdata/Health - Life Expectancy/WHO_LifeExpectancy.csv", clear
			
		keep if period==2000 | period==2019
		
		keep indicator spatialdimvaluecode location period dim1 value
		
		ren location country
		ren spatialdimvaluecode country_code
		ren dim1 group
		ren period year
		
		compress
		
		gen temp=0 if indicator=="Life expectancy at birth (years)"
		replace temp=60 if indicator=="Life expectancy at age 60 (years)"
		
		drop indicator
		
		reshape wide value, i(country_code country year group) j(temp)
		
		replace group="total" if group=="Both sexes"
		replace group="male" if group=="Male"
		replace group="female" if group=="Female"
		
		reshape wide value0 value60,  i(country_code country year) j(group) string
		
		ren value60* lifeexpsixty*
		ren value0* lifeexpbirth*
		
		tempfile lifeexpectancies
		save `lifeexpectancies', replace
		
	** Healthy life expectancy 

		insheet using "$dir/rawdata/Health - Life Expectancy/WHO_HealthyLifeExpectancy.csv", clear
		
		keep if period==2000 | period==2019
		
		keep indicator spatialdimvaluecode location period dim1 value
		
		ren location country
		ren spatialdimvaluecode country_code
		ren dim1 group
		ren period year
		
		compress
		
		gen temp=0 if indicator=="Healthy life expectancy (HALE) at birth (years)"
		replace temp=60 if indicator=="Healthy life expectancy (HALE) at age 60 (years)"
		
		drop indicator
		
		reshape wide value, i(country_code country year group) j(temp)
		
		replace group="total" if group=="Both sexes"
		replace group="male" if group=="Male"
		replace group="female" if group=="Female"
		
		reshape wide value0 value60,  i(country_code country year) j(group) string
		
		ren value60* healthylifeexpsixty*
		ren value0* healthylifeexpbirth*
		
		tempfile healthylifeexpectancies
		save `healthylifeexpectancies', replace
		
	** Total population
	
		insheet using "$dir/rawdata/Population/API_SP.POP.TOTL_DS2_en_csv_v2_5358404.csv", names clear
		
			drop if _n==1
			ren datasource country
			ren worlddevelopmentindicators country_code
			drop v3 v4
			foreach var of varlist v5-v67 {
				di "** `var'"
				sum `var' if _n==1
				ren `var' population`r(mean)'
			}
			drop population
			drop if _n==1
			
			keep country country_code population2019
			
			tempfile pop
			save `pop', replace
	
	** Regional classification
	
		import excel using "$dir/rawdata/Income Groups/CLASS.xlsx", firstrow clear
		
			drop Lendingcategory OtherEMUorHIPC
			
			ren Economy country
			ren Code country_code
			ren Region region
			ren Incomegroup incomegroup
		
			drop if missing(region)
			
			replace country=strtrim(country)
			
			replace country="Cote d'Ivoire" if strpos(country,"Ivoire")==11
			replace country="Curacao" if country=="Curaçao"
			replace country="Czechia" if country=="Czech Republic"
			replace country="Turkiye" if country=="Türkiye"
			replace country="Sao Tome and Principe" if country=="São Tomé and Príncipe"
			
			replace incomegroup="Upper middle income" if country=="Venezuela, RB"
			replace region="Sub-Saharan Africa" if country=="Djibouti" // to align with UNDP definition
			replace region="Middle East & North Africa" if country=="Sudan" // to align with UNDP definition
			
			tempfile incomegroups
			save `incomegroups', replace
			
	** Merge and summarize
			 
		merge 1:1 country_code using `pop', gen(merge)
			tab merge
			keep if merge==3
			drop merge

		merge 1:m country_code using `lifeexpectancies', assert(master matched) keep(matched) nogen
		merge 1:1 country_code year using `healthylifeexpectancies', assert(master matched) keep(matched) nogen
		
		sum lifeexpbirthtotal [aw=population2019] if region=="Sub-Saharan Africa" & year==2000
		sum lifeexpbirthtotal [aw=population2019] if region=="Sub-Saharan Africa" & year==2019
		
		sum healthylifeexpbirthtotal [aw=population2019] if region=="Sub-Saharan Africa" & year==2000
		sum healthylifeexpbirthtotal [aw=population2019] if region=="Sub-Saharan Africa" & year==2019
